The invention relates to database processing method and apparatus and, more particularly, to database processing method and apparatus which can define a Large OBject data type (LOB type) and an Abstract Data Type (ADT type).
A database management system (hereinbelow, abbreviated to DBMS), particularly, a relational DBMS processes an inquiry expressed by a non-procedure like language. SQL is used as a database language. With respect to the database language SQL, studies regarding the standard of the next SQL (ISO, ANSI) are being progressed at present.
The LOB type is one of main functions of the next SQL. The LOB type is a type which enables the DBMS to handle data such as document, moving picture, audio data, or the like of an amount that is extremely larger than that of data which has hitherto been handled by a conventional database. In accordance with a type of data to be stored, a CLOB type for storing character data such as characters or the like and a BLOB type for storing binary data such as image, audio, or the like are prepared.
As for data such as moving picture, audio, or the like, since a size of each data is large, data of a few megabytes or gigabytes is also considered. It is difficult for the user to store or read out data of the LOB type to/from the DBMS in a lump. Therefore, an interface to divisionally write or read out data by a plurality of operations is generally used.
An example of the operation for such LOB data is shown below. An LOB locator is used in order to hold the LOB data as variables in the DBMS and to operate the LOB data by an UAP (user application). Operation examples are shown by a description by a C language. A sentence structure which begins with xe2x80x9cEXEC SQLxe2x80x9d buried in the C language program is a description of the DBMS operation.
EXEC SQL BEGIN DECLARE SECTION;
SQL TYPE IS BLOB_LOCATOR resume;
char buffer[1000]=xe2x80x9c xe2x80x9d;
int number;
int start;
int end;
EXEC SQL END DECLARE SECTION;
In the above description, variables defined within a range from xe2x80x9cSQL BEGIN DECLARE SECTIONxe2x80x9d to xe2x80x9cEND DECLARE SECTIONxe2x80x9d denote a definition of the variables which are used for the DB operation. xe2x80x9cresumexe2x80x9d denotes the LOB locator.
EXEC SQL DECLARE cl CURSOR FOR
SELECT no, resume FROM emp_resume WHERE
format=xe2x80x98asciixe2x80x99;
EXEC SQL OPEN cl;
EXEC SQL FETCH cl INTO :number, :resume;
It denotes a description indicating that a retrieval for obtaining values of xe2x80x9cnoxe2x80x9d and xe2x80x9cresumexe2x80x9d in the line of xe2x80x9cformat=xe2x80x98asciixe2x80x99xe2x80x9d in an xe2x80x9cemp_resumexe2x80x9d table is executed. It is assumed that the xe2x80x9cemp_resumexe2x80x9d table has previously been defined. First, a cursor xe2x80x9cclxe2x80x9d is defined by xe2x80x9cDECLARE CURSORxe2x80x9d and the retrieving condition is designated by xe2x80x9cSELECTxe2x80x9d. The cursor xe2x80x9cclxe2x80x9d is opened by next xe2x80x9cOPENxe2x80x9d and one line adapted to the above condition is extracted by next xe2x80x9cFETCHxe2x80x9d. Now assuming that the xe2x80x9cresumexe2x80x9d column is of the LOB type, a locator value indicative of the LOB data is set to xe2x80x9c:resumexe2x80x9d.
EXEC SQL SET :buffer=SUBSTR(:resume, :start, :size);
It is a description indicating that a SUBSTR function is executed to the LOB data indicated by the xe2x80x9c:resumexe2x80x9d locator and the result is stored into a xe2x80x9cbufferxe2x80x9d. The SUBSTR function is a process for extracting data within a range from a xe2x80x9c:startxe2x80x9d position to a xe2x80x9c:sizexe2x80x9d size of the LOB data shown by the xe2x80x9c:resumexe2x80x9d locator.
EXEC SQL SET :resume=xe2x80x9cHello !!xe2x80x9d;
It is a description indicating that xe2x80x9cHello !!xe2x80x9d is set to the LOB data shown by the xe2x80x9c:resumexe2x80x9d locator (namely, data is substituted into variables).
EXEC SQL INSERT into emp_resume values(:number, :resume);
It is a description indicating that an integer value of xe2x80x9c:numberxe2x80x9d and the LOB data shown by the xe2x80x9c:resumexe2x80x9d locator are stored into the xe2x80x9cemp_resumexe2x80x9d table (namely, an insertion of data into an actual database).
As for the LOB data, the operation as mentioned above can be performed.
ADT (Abstract Data Type) is also one of the main functions of the next SQL. ADT is a data type of a user definition in which a type defined by the user can be designated when a table is defined in a manner similar to a type of a system definition. Since the ADT type is a data type formed by the user and is a data type which is not known by the DB, it is difficult to extract the ADT type data by the UAP such as a C language. For this purpose, an operating method using an ADT locator is provided. In a manner similar to the case of the LOB type, an example of the operation for the ADT type data is shown below.
EXEC SQL CREATE VALUE TYPE real_estate
It is a description indicating that a xe2x80x9creal_estatexe2x80x9d type of the user definition is defined.
EXEC SQL CREATE VALUE TYPE address
EXEC SQL CREATE VALUE TYPE doc
It is a description indicating that an xe2x80x9caddressxe2x80x9d type and a xe2x80x9cdocxe2x80x9d type are defined in a manner similar to the definition of the xe2x80x9creal_estatexe2x80x9d type.
EXEC SQL CREATE TABLE real_estate_info
It is a description indicating that a xe2x80x9creal_estate_infoxe2x80x9d table is defined. Particularly, a column of xe2x80x9cproperxe2x80x9d in the table is set to the xe2x80x9creal_estatexe2x80x9d type of the user definition.
EXEC SQL BEGIN DECLARE SECTION
SQL TYPE IS ADT_LOCATOR FOR real_estate re_locator;
SQL TYPE IS BLOB(2G) doc_file;
struct{
EXEC SQL END DECLARE SECTION;
Variables defined within a range from xe2x80x9cSQL BEGIN DECLARE SECTIONxe2x80x9d to xe2x80x9cEND DECLARE SECTIONxe2x80x9d in the above description denote a definition of the variables which is used for the DB operation. xe2x80x9cre_locatorxe2x80x9d indicates the ADT locator.
EXEC SQL DECLARE rel CURSOR FOR
SELECT property FROM real_estate_info WHERE price less than 400000 FOR UPDATE OF property;
EXEC SQL OPEN rel;
In the above description, a cursor xe2x80x9crelxe2x80x9d is defined by xe2x80x9cDECLARE CURSORxe2x80x9d and a retrieving condition is designated by xe2x80x9cSELECTxe2x80x9d. A retrieving condition is a retrieval for obtaining xe2x80x9cpropertyxe2x80x9d in the line of xe2x80x9cprice less than 400000xe2x80x9d of the xe2x80x9creal estate_infoxe2x80x9d table in order to update xe2x80x9cpropertyxe2x80x9d. After that, the cursor xe2x80x9crelxe2x80x9d is opened by xe2x80x9cOPENxe2x80x9d.
while(SQLCODE==O){
EXEC SQL FETCH rel INTO :re_locator;
EXEC SQL SET :text_descr=text_description(:re_locator);
EXEC SQL SET :doc_file=data(document(:re_locator));
EXEC SQL UPDATE real_estate_info
SET property=:re_locator WHERE CURRENT OF rel;
}
EXEC SQL CLOSE rel;
The operation which is executed in the above xe2x80x9cwhilexe2x80x9d sentence will now be sequentially explained. First, one line is extracted by the cursor xe2x80x9crelxe2x80x9d by xe2x80x9cFETCHxe2x80x9d. It denotes that the setting of the current position and the positioning of the ADT locator (a locator value indicative of the ADT data is set to xe2x80x9c:re_locatorxe2x80x9d) are performed. The next xe2x80x9cSETxe2x80x9d sentence indicates that the operation to designate xe2x80x9c:re_locatorxe2x80x9d as an ADT locator and to extract xe2x80x9ctext_descriptionxe2x80x9d of a variable length character train from xe2x80x9cpropertyxe2x80x9d as a xe2x80x9creal_estatexe2x80x9d type of the user definition is performed. The next xe2x80x9cSETxe2x80x9d sentence denotes that the operation to designate xe2x80x9c:re_locatorxe2x80x9d as an ADT locator, to extract xe2x80x9cdocumentxe2x80x9d as a xe2x80x9cdocxe2x80x9d type from xe2x80x9cpropertyxe2x80x9d as a xe2x80x9creal_estatexe2x80x9d type of the user definition, to extract BLOB data xe2x80x9cdataxe2x80x9d in the xe2x80x9cdocxe2x80x9d type data, and to transfer it to a client is executed. The next xe2x80x9cUPDATExe2x80x9d indicates the execution of the operation to reflect the value of the ADT data designated by the ADT locator xe2x80x9cre_locatorxe2x80x9d to the DB (xe2x80x9creal_estate_infoxe2x80x9d table). After the xe2x80x9cwhilexe2x80x9d sentence, the cursor xe2x80x9crelxe2x80x9d is closed by xe2x80x9cCLOSExe2x80x9d.
The following operation can be executed with respect to the ADT data.
The locator has been disclosed in Don Chamberlin, xe2x80x9cUsing The New DB2xe2x80x9d, pp. 236-241, 1996.
On the other hand, in recent years, the application of the relational DBMS has been being progressed mainly with respect to a data process of business affairs, so that a database system which can scalably cope with increases in transaction amount and database amount exceeding a progress of a performance of a CPU or a disk capacity has been strongly required. In response to the request, a parallel DBMS has been proposed by various companies. With respect to a parallel DBMS architecture, for example, there have been proposed three systems as described in FIG. 1 of the article disclosed in xe2x80x9cNikkei Electronicsxe2x80x9d, pages 101 to 114, Feb. 27, 1995. The three systems are a Shared-Everything system in which a main storage and a hard disk drive (HDD) are shared by all processors, a Shared-Disk system in which only the HDD is shared among the processors and the main storage is provided for every processor, and a Shared-Nothing system in which none of the main storage and the HDD is not shared among the processors.
In the parallel DBMS, servers are connected by a network and all of a notification of a control, a movement of data, and the like are performed by communication. Therefore, the movement of data of a large size such as LOB data causes an increase in communication load, so that a load of the whole system is raised.
Although LOB data or ADT data can be stored or read out to/from a DBMS by the foregoing conventional technique, such processes correspond to instructions of the operation between the user application program (UAP) and the DBMS.
In case of processing data of the LOB type in a parallel DBMS, a transfer of LOB data among servers occurs. For example, a parallel DBMS having a construction as shown in FIG. 2 is considered. In this case, an inquiry request from the user is accepted by a request accepting server and an actual execution for the inquiry is performed by a DB processing server. Considering a case of handling the LOB data, for example, the following operations in (1) and (2) are performed. (1) The LOB data that is sent from the user is transmitted to the DB processing server via the request accepting server.
(2) In case of extracting the LOB data in the DB, the LOB data is transferred from the DB processing server in which the LOB data has been stored to the request accepting server and is further returned to the user.
The movement of the data of a large size such as LOB data among the servers, however, causes an inconvenience that a load of the whole system is raised as mentioned above. On the other hand, even when a retrieval is designated, all of the retrieval results are not always extracted. A case where the retrieval result is directly used as an insertion value to the other table is also considered. In this case, it is efficient that the LOB data is transmitted from a storage source to the DB processing server as an insertion destination without being transmitted via the request accepting server.
There is a handle as a pointer indicative of an object. It is known that storage destination information of management target data such as LOB data or the like is managed by the handle. The xe2x80x9chandlexe2x80x9d has been disclosed in, for example, Robert B. Murray, xe2x80x9cC++ Strategies and Tacticsxe2x80x9d, published by Soft Bank Co. Ltd., pp. 57-73, Jan. 30, 1994. In this case, however, the handle is used by only one computer and the storage destination of data is not managed among a plurality of computers by using the handle. The use of the handle in the database system is not proposed yet.
It is an object of the invention to provide database processing method and apparatus in which when data of a predetermined type is operated in a parallel DB system, a communication load of the whole system is reduced.
Another object of the invention is to provide database processing method and apparatus in which when data of a predetermined type is operated in a parallel DB system, a movement amount and the number of moving times of real data are reduced and an unnecessary data transfer is avoided, thereby reducing a communication load of the whole system.
To accomplish the above object, according to one aspect of the invention, there is provided with a database processing method in a parallel database system which has request accepting servers for accepting a processing request of data of a predetermined type and analyzing the request and allocating a process corresponding to the request to any one of the request accepting servers and at least one of database processing servers and the at least one database processing server for executing the process allocated by the request accepting servers, and in which the request accepting servers and the at least one database processing server are mutually connected through a network, comprising the steps of:
a) preparing a handle (FIG. 4) serving as a control block for managing a storage area of data as a processing target;
b) when the data as a processing target corresponding to the processing request is processed in accordance with the processing request, receiving and transmitting the handle regarding the processing target data corresponding to the processing request between the request accepting server and the database processing server to which the above process was allocated and executing a process according to the processing request; and
c) obtaining an entity of the processing target data on the basis of the handle when the entity of the processing target data is necessary in the step b) and processing.
According to another aspect of the invention, there is provided with a database processing method in a parallel database system which has request accepting servers for accepting a processing request of data of a predetermined type and analyzing the request and allocating a process corresponding to the request to any one of the request accepting servers and the at least of database processing servers and the at least one database processing server for executing the process allocated by the request accepting servers, and in which the request accepting servers and the at least one database processing server are mutually connected through a network, comprising the steps of:
a) preparing a server which is a control block for managing a storage area of data as a processing target and in which the data as a processing target has been stored and a handle having information with respect to a storing location;
b) when the data as a processing target corresponding to the processing request is processed in accordance with the processing request, executing a process according to the processing request by the request accepting server by using the handle regarding the processing target data; and
c) when an entity of the processing target data is necessary in the step b), obtaining the entity of the processing target data on the basis of the handle and processing.
According to an example of the invention:
in the step a), as such a handle, a handle having a server identifier to identify the server in which the data as a processing target corresponding to the processing request has been stored and storing position information indicative of a position in the server at which the processing target data has been stored is prepared;
in the step b), the data as a processing target corresponding to the processing request is designated by using a locator which is associated in the processing request and designates the handle as a parameter; and
in the step c), when the entity of the processing target data is necessary, the entity of the processing target data which is identified by the server identifier and storing position information of the handle is obtained with reference to the handle that is designated by the locator.
According to an example of the invention:
as a location to store the data of the predetermined type, there are a memory in the server, a temporary file of an external storage device connected to the server, or a database of the external storage device connected to the server; and
in the step a), as the storing position information, a handle having a flag indicating in which one of the memory in the server, the temporary file of the external storage device connected to the server, and the database of the external storage device connected to the server the data has been stored and an address in which the data has been stored is prepared.
According to an example of the invention, in the step a), as such a handle, a handle having an area to store an access counter value indicative of the number of references sharing the data as a processing target corresponding to the processing request is further prepared.
In the invention, as mentioned above, the process according to the processing request is executed by using the handle as a control block to manage the storage area of the data as a processing target, namely, the handle having the server identifier to identify the server in which the processing target data corresponding to the processing request has been stored and the storing position information indicative of the position in the server in which the processing target data has been stored. Therefore, in cases other than the case where the entity of the processing target data is necessary, the handle is moved among the servers, and when the entity of the processing target data is necessary, the entity of the processing target data is obtained on the basis of the handle. Therefore, the movement amount and the number of moving times of the entity of the unnecessary data can be reduced and the communication load of the whole parallel database system can be reduced. Particularly, the invention is effective in case of handling data of a large size such as LOB data.